# coding=gbk

from DataAccess.blp_client import query_blp_data
from DataAccess.DBConnFactory import DBConnFactory
from Error.ExtendError import *
from Misc.xlDate import read_xl_date
from Misc.OpenExcel import open_xlsheet
from Misc.Utils import *

from string import Template


OVERSEA_FUND='SC028'


def check_existence(sql_text, cursor):
	cursor.execute(sql_text)
	r = cursor.fetchone()
	if r and len(r) > 0:    #already exist
		return (True, r[0])
	else:
		return (False, None)
		
	
def import_pnote(pkedb_conn,trade_record,portfolio_id):
	cursor = pkedb_conn.cursor()
	
	#trade_record=[trade_date,ticker,direction,quantity,cost,currency,security_type,settle_date]
	trade_date=trade_record[0]
	isin=trade_record[1]+' Corp'
	trade_type=trade_record[2]
	security_type='PNOTE'
	amount=trade_record[3]
	cost_price=trade_record[4]
	currency=trade_record[5]
	settle_date=trade_record[7]

	#insert trade
	sql_text = '''merge into transaction trd
	            using (select TO_DATE(:1,'yyyy-mm-dd') as trade_date,
							  :2 as ticker,
							  :3 as trade_type,
							  :4 as portfolio_id,
							  :5 as security_type,
							  :6 as amount,
							  :7 as cost_price,
							  :8 as currency,
							  :9 as settle_date
					    from dual) t
				on (trd.trade_date=t.trade_date and trd.ticker=t.ticker and trd.portfolio_id=t.portfolio_id and trd.security_type=t.security_type)
				when matched then update set
					trd.trade_type=t.trade_type,
					trd.amount=t.amount,
					trd.cost_price=t.cost_price,
					trd.currency=t.currency,
					trd.settle_date=t.settle_date
				when not matched then insert (trd.trade_date,trd.ticker,trd.trade_type,trd.portfolio_id,trd.security_type,trd.amount,trd.cost_price,trd.currency,trd.settle_date)
					values(t.trade_date,t.ticker,t.trade_type,t.portfolio_id,t.security_type,t.amount,t.cost_price,t.currency,t.settle_date)'''
					
	cursor.execute(sql_text, 
			(trade_date,isin,trade_type.upper(),portfolio_id,security_type,amount,cost_price,currency,settle_date))
	pkedb_conn.commit()
	
	if check_existence(Template('''select * from pnote_info where isin_code='${ISIN}' ''').substitute(ISIN=isin), cursor)[0]:
		return
	
	
	#currency checking with blp
	r_blp=query_blp_data([isin], ['CRNCY','UNDERLYING_SECURITY_DES'])
	if len(r_blp[isin])==0:
		raise BLPDataError(), "Failed to get data for " + isin
		
	pnote_curncy=r_blp[isin]['CRNCY'].upper()
	underlying_blp_ticker=r_blp[isin]['UNDERLYING_SECURITY_DES']
	
	r_blp=query_blp_data([underlying_blp_ticker], ['CRNCY'])
	if len(r_blp[underlying_blp_ticker])==0:
		raise BLPDataError(), "Failed to get data for " + underlying_blp_ticker
	underlying_currency=r_blp[underlying_blp_ticker]['CRNCY'].upper()	

	if pnote_curncy!=trade_record[5]:
		print 'warn, currency of PNOTE is not same as blp ', pnote_curncy
		return
	
	#underlying info
	underlying_ticker=blp_ticker_to_ch_ticker(underlying_blp_ticker)
	
	#update pnote_info table 
	sql_tpl = Template('''merge into pnote_info pi
					using (select '${ISIN}' isin, '${TICKER}' uly_tk, '${CURNCY}' curncy, '${UNDERLY_CURNCY}' uly_curncy, 'STOCK' uly_type from dual) t
					on (pi.isin_code=t.isin)
					when matched then update set 
						pi.underlying_ticker=t.uly_tk,
						pi.currency=t.curncy,
						pi.underlying_currency=t.uly_curncy,
						pi.underlying_type = t.uly_type
					when not matched then insert 
						values(t.isin,uly_tk,curncy,uly_curncy,t.uly_type)''')
	sql_text=sql_tpl.substitute(ISIN=isin, 
								TICKER=underlying_ticker, 
								CURNCY=currency,
								UNDERLY_CURNCY=underlying_currency)	
	pkedb_conn.cursor().execute(sql_text)
	
	sql_tpl = Template('''merge into comm_security_static_info s
					using (select '${ISIN}' tk,"name" from comm_security_static_info
							where ticker='${ULY_TK}') t
					on (t.tk=s.ticker)
					when matched then update set 
						s."name"=t."name"
					when not matched then insert(ticker,"name")
						values(t.tk,t."name")''')
	pkedb_conn.cursor().execute(sql_tpl.substitute(ISIN=isin, ULY_TK=underlying_ticker))
	pkedb_conn.commit()
	
	
def import_futures(pkedb_conn,trade_record,portfolio_id):	
	cursor = pkedb_conn.cursor()

	#trade_record=[trade_date,ticker,direction,quantity,cost,currency,security_type,settle_date]
	trade_date=trade_record[0]
	ticker=trade_record[1]
	trade_type=trade_record[2]
	security_type='FUTURES'
	amount=trade_record[3]
	cost_price=trade_record[4]
	currency=trade_record[5]
	settle_date=trade_record[7]

	sql_text = '''merge into transaction trd
	            using (select TO_DATE(:1,'yyyy-mm-dd') as trade_date,
							  :2 as ticker,
							  :3 as trade_type,
							  :4 as portfolio_id,
							  :5 as security_type,
							  :6 as amount,
							  :7 as cost_price,
							  :8 as currency,
							  :9 as settle_date
					    from dual) t
				on (trd.trade_date=t.trade_date and trd.ticker=t.ticker and trd.portfolio_id=t.portfolio_id and trd.security_type=t.security_type)
				when matched then update set
					trd.trade_type=t.trade_type,
					trd.amount=t.amount,
					trd.cost_price=t.cost_price,
					trd.currency=t.currency,
					trd.settle_date=t.settle_date
				when not matched then insert (trd.trade_date,trd.ticker,trd.trade_type,trd.portfolio_id,trd.security_type,trd.amount,trd.cost_price,trd.currency,trd.settle_date)
					values(t.trade_date,t.ticker,t.trade_type,t.portfolio_id,t.security_type,t.amount,t.cost_price,t.currency,t.settle_date)'''
					
	pkedb_conn.cursor().execute(sql_text, 
				(trade_date,ticker,trade_type.upper(),portfolio_id,security_type,amount,cost_price,currency,settle_date))
	pkedb_conn.commit()
	
	
	if check_existence(Template('''select * from comm_security_static_info where ticker='${TK}' ''').substitute(TK=ticker), cursor)[0]:
		return
		
	#currency checking with blp
	#futures name and point value
	r_blp=query_blp_data([ticker], ['SHORT NAME','FUT_VAL_PT','CRNCY','EXCH_CODE'])
	if len(r_blp[ticker])==0:
		raise BLPDataError(), "Failed to get data for " + ticker
	
	#print ticker
	#print r_blp
	futures_name=r_blp[ticker]['SHORT NAME']
	point_value=r_blp[ticker]['FUT_VAL_PT']
	curr_blp=r_blp[ticker]['CRNCY'].upper()
	market=r_blp[ticker]['EXCH_CODE']
	
	if curr_blp!=trade_record[5]:
		print 'warn, currency of Futures is not same as blp ', curr_blp
		return
		

	#update futures info table 		
	sql_tpl = Template('''merge into comm_security_static_info s
						using (select '${TICKER}' tk, '${NAME}' nm from dual) t
							on (t.tk=s.ticker)
						when matched then update 
							set s."name"=t.nm
						when not matched then insert(ticker,"name") 
							values (t.tk,t.nm)''')
	sql_text=sql_tpl.substitute(TICKER=ticker, NAME=futures_name)
	pkedb_conn.cursor().execute(sql_text)
	
	sql_tpl = Template('''merge into comm_misc_static_info s 
						using (select '${TICKER}' tk, '${FIELD}' f, '${VALUE}' v from dual) t
							on (t.tk=s.ticker and t.f=s.field)
						when matched then update set 
							s."value"=t.v
						when not matched then insert (ticker,field,"value") values  
							(t.tk,t.f,t.v)''')
	sql_text=sql_tpl.substitute(TICKER=ticker, 
								FIELD='FUT_VAL_PT',
								VALUE=point_value)
	pkedb_conn.cursor().execute(sql_text)
	pkedb_conn.commit()			

		
def import_comm_stock(pkedb_conn,trade_record,portfolio_id):
	cursor = pkedb_conn.cursor()

	#trade_record=[trade_date,ticker,direction,quantity,cost,currency,security_type,settle_date]
	trade_date=trade_record[0]
	ticker=trade_record[1]
	trade_type=trade_record[2]
	amount=trade_record[3]
	cost_price=trade_record[4]
	currency=trade_record[5]
	settle_date=trade_record[7]
	
	sys_ticker = ticker
	if blp_ticker_is_hk_ticker(ticker):
		sys_ticker = blp_ticker_to_hk_ticker(ticker)
		
	r = check_existence(Template('''select market from comm_security_static_info where ticker='${TK}' ''').substitute(TK=sys_ticker), cursor)	
	if r[0]:
		market = r[1] 
	else:
		#currency checking with blp
		r_blp=query_blp_data([ticker], ['CRNCY','EXCH_CODE','SHORT NAME'])
		if len(r_blp[ticker])==0:
			raise BLPDataError(), "Failed to get data for " + ticker

		curr_blp=r_blp[ticker]['CRNCY'].upper()
		market=r_blp[ticker]['EXCH_CODE']
		stock_name=r_blp[ticker]['SHORT NAME']
		
		if curr_blp!=trade_record[5]:
			print 'warn, currency of stock is not same as blp ', curr_blp
			return
				
		#update common stock info table			
		sql_tpl = Template('''merge into comm_security_static_info s
							using (select '${TICKER}' tk,'${NAME}' nm, '${MKT}' mkt from dual) t
								on (t.tk=s.ticker)
							when matched then update set
								s."name"=t.nm,
								s.market=t.mkt
							when not matched then insert values
								(t.tk,t.nm,t.mkt)''')
		sql_text=sql_tpl.substitute(TICKER=sys_ticker, NAME=stock_name, MKT=market)
		pkedb_conn.cursor().execute(sql_text)
		pkedb_conn.commit()		
		
	
	#insert trade
	sql_text = '''merge into transaction trd
	            using (select TO_DATE(:1,'yyyy-mm-dd') as trade_date,
							  :2 as ticker,
							  :3 as trade_type,
							  :4 as portfolio_id,
							  'STOCK' as security_type,
							  :5 as amount,
							  :6 as cost_price,
							  :7 as currency,
							  :8 as market,
							  :9 as settle_date
					    from dual) t
				on (trd.trade_date=t.trade_date and trd.ticker=t.ticker and trd.portfolio_id=t.portfolio_id and trd.security_type=t.security_type)
				when matched then update set
					trd.trade_type=t.trade_type,
					trd.amount=t.amount,
					trd.cost_price=t.cost_price,
					trd.currency=t.currency,
					trd.market=t.market,
					trd.settle_date=t.settle_date
				when not matched then insert (trd.trade_date,trd.ticker,trd.trade_type,trd.portfolio_id,trd.security_type,trd.amount,trd.cost_price,trd.currency,trd.market,trd.settle_date)
					values(t.trade_date,t.ticker,t.trade_type,t.portfolio_id,t.security_type,t.amount,t.cost_price,t.currency,t.market,t.settle_date)'''
					
	pkedb_conn.cursor().execute(sql_text, 
					(trade_date,sys_ticker,trade_type.upper(),portfolio_id,amount,cost_price,currency,market,settle_date))
	pkedb_conn.commit()

	
def import_warrant(pkedb_conn,trade_record,portfolio_id):

    #trade_record=[trade_date,ticker,direction,quantity,cost,currency,security_type,settle_date]	
	trade_date=trade_record[0]
	ticker=trade_record[1]
	trade_type=trade_record[2]
	amount=trade_record[3]
	cost_price=trade_record[4]
	currency=trade_record[5]
	sec_type=trade_record[6]
	settle_date=trade_record[7]
	
	#insert trade
	sql_text = '''merge into transaction trd
	            using (select TO_DATE(:1,'yyyy-mm-dd') as trade_date,
							  :2 as ticker,
							  :3 as trade_type,
							  :4 as portfolio_id,
							  :5 as security_type,
							  :6 as amount,
							  :7 as cost_price,
							  :8 as currency,
							  :9 as settle_date
					    from dual) t
				on (trd.trade_date=t.trade_date and trd.ticker=t.ticker and trd.portfolio_id=t.portfolio_id and trd.security_type=t.security_type)
				when matched then update set
					trd.trade_type=t.trade_type,
					trd.amount=t.amount,
					trd.cost_price=t.cost_price,
					trd.currency=t.currency,
					trd.settle_date=t.settle_date
				when not matched then insert (trd.trade_date,trd.ticker,trd.trade_type,trd.portfolio_id,trd.security_type,trd.amount,trd.cost_price,trd.currency,trd.settle_date)
					values(t.trade_date,t.ticker,t.trade_type,t.portfolio_id,t.security_type,t.amount,t.cost_price,t.currency,t.settle_date)'''
	
	pkedb_conn.cursor().execute(sql_text, 
					(trade_date,ticker,trade_type.upper(),portfolio_id,sec_type,amount,cost_price,currency,settle_date))
	pkedb_conn.commit()	
	
	
def read_trade(trade_date,file_path,sheet_index=0):
	portfolio_id = OVERSEA_FUND
	
	ws_handle=open_xlsheet(file_path,sheet_index)
	if not ws_handle:
		print '    error, Step 1.4: open CH-OPP trade file FAILED.'
		return
		#raise IOError, ('Can not open xls file, ' + file_path)
	
	pkedb_conn = DBConnFactory().get_db_connection('PKEDB')
	
	trade_record = {}
	
	num_rows=ws_handle.nrows
	for row in range(2,num_rows):
		
		xldate = ws_handle.cell_value(row, 8)
		ticker = ws_handle.cell_value(row, 9)
		if xldate and ticker:
	
			if read_xl_date(xldate)==trade_date: 
				direction=ws_handle.cell_value(row, 10)
				quantity=ws_handle.cell_value(row, 11)
				cost=ws_handle.cell_value(row, 12)
				currency=ws_handle.cell_value(row, 13)
				security_type=ws_handle.cell_value(row, 14)
				settle_date=read_xl_date(ws_handle.cell_value(row, 15))
				
				#trade_record=[trade_date.strftime('%Y-%m-%d'),ticker,direction,quantity,cost,currency,security_type,settle_date]
				if trade_record.has_key((trade_date, ticker)):
				
					if direction != trade_record[(trade_date, ticker)][0]:
						print 'err info, more than trade direction for trade: ', trade_date.isoformat(), ticker
						return				
					elif currency != trade_record[(trade_date, ticker)][3]:
						print 'err info, more than one currency for trade: ', trade_date.isoformat(), ticker
						return
					elif security_type != trade_record[(trade_date, ticker)][4]:
						print 'err info, more than one security type for trade: ', trade_date.isoformat(), ticker
						return
					elif settle_date != trade_record[(trade_date, ticker)][5]:
						print 'err info, more than one settle date for trade: ', trade_date.isoformat(), ticker
						return
					else:
						pre_quantity = trade_record[(trade_date, ticker)][1]
						pre_cost = trade_record[(trade_date, ticker)][2]
						
						trade_record[(trade_date, ticker)][1] = pre_quantity + quantity
						trade_record[(trade_date, ticker)][2] = (pre_quantity*pre_cost + quantity*cost)/(pre_quantity + quantity)	
				else:
					trade_record[(trade_date, ticker)] = [direction, quantity, cost, currency, security_type, settle_date]

				#print 'info,trade info: ', trade_record
	for k, v in trade_record.iteritems():
	
		trd = [k[0].strftime('%Y-%m-%d'), k[1], v[0], v[1], v[2], v[3], v[4], v[5]]
		
		if v[4] == 'PNOTE':
			import_pnote(pkedb_conn,trd,portfolio_id)
		elif v[4] == 'FUTURES':
			import_futures(pkedb_conn,trd,portfolio_id)
		elif v[4] == 'WARRANT':
			import_warrant(pkedb_conn,trd,portfolio_id)
		elif v[4] == 'STOCK':
			import_comm_stock(pkedb_conn,trd,portfolio_id)
		else:
			continue
						
	print '    info, Step 1.4: CH-OPP trades import completed.'
		
		
		
		
		
		
		

